Importing libraries¶
In [2]:
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import plotly.express as px
from matplotlib.pyplot import plot
import seaborn as sns
%matplotlib inline
from sqlalchemy import create_engine
import psycopg2
from datetime import timedelta, date, datetime
import pandas as pd
import urllib
from sqlalchemy import create_engine
from matplotlib import style
import warnings
warnings.filterwarnings("ignore")
import os
from sklearn.neighbors import NearestNeighbors
from random import sample
from numpy.random import uniform
import numpy as np
from math import isnan
from sklearn.preprocessing import LabelEncoder
from sklearn.decomposition import PCA
from sklearn.preprocessing import PowerTransformer
import numpy as np
import pandas as pd
from datetime import datetime
from tqdm import tqdm
from sklearn.preprocessing import PowerTransformer
import matplotlib.pyplot as plt
import plotly.graph_objects as go
from scipy import stats
from sklearn.cluster import KMeans
from sklearn.model_selection import train_test_split, StratifiedKFold, GridSearchCV, cross_validate
import sklearn.metrics as metrics
from sklearn.model_selection import cross_val_score
import pickle
Importing dataset and loading it into PosgreSQL dataBase created using 'pgAdmin 4'¶
- This dataset was obtained from this link in kaggle -> https://www.kaggle.com/camesruiz/colombia-covid-19-insights/data
The data consider just March of 2020
In [3]:
cases = pd.read_csv('Casos1.csv')
- First review of the dataset
In [4]:
cases.head()
Out[4]:
- Loading the table into the database 'PlatziDB' created just for this purpose.
In [ ]:
engine = create_engine('postgresql://postgres:olimac@localhost:5432/PlatziDB')
cases.to_sql('cases', engine)
- Getting the table from PosgreSQL PlatziDB database. This table is saved in a dataframe and some labels of the table are adjusted.
In [5]:
conn = psycopg2.connect('dbname=PlatziDB user=postgres host=localhost password=olimac')
cur = conn.cursor()
cur.execute('''SELECT * FROM cases;''')
DB_cases = pd.DataFrame(list(cur.fetchall()))
DB_cases.columns = ["ID_sql","ID", "date", "city", "departamento", "state", "age", "sex", "type", "procedence"]
cols_ = DB_cases.select_dtypes(include=[np.object]).columns
DB_cases[cols_] = DB_cases[cols_].apply(lambda x: x.str.normalize('NFKD').str.encode('ascii', errors='ignore').str.decode('utf-8'))
DB_cases.head()
Out[5]:
- In this dataset there are 8 relevant columns to analyse.
- Date: it considers only march of 2020.
- City: City of Colombia.
- Department: Department of colombia.
- state: Current state of the infected person.
- Age: Age of the person infected.
- Sex: Sex of the person infected.
- Type: Kind of person infected, (Ex, if the person comes from other country)
- Procedence: Contry of origin if the case was imported
Understanding the database¶
In [7]:
print("Number of cases: " + str(len(DB_cases)))
print("The timeframe considered is between " + str(min(DB_cases['date'])) + " and " + str(max(DB_cases['date'])))
- Is there any Null value in the dataset?
In [8]:
DB_cases.isnull().sum()
Out[8]:
- As there is not Null value in the data I will start with the analysis.
- In the following part, I want to undertand the distribution of cases per department in order to priorize by percentage the most important ones to focus.
- The top three deparments are: Bogota DC, Valle del Cauca and Antioquia with around 68% of participation over total cases in march. The following chart displays the number of cases per department sorted. In [9]:
order_percent = DB_cases.departamento.value_counts(normalize = True)
print(order_percent)
plt.figure(figsize=(15,8))
sns.countplot(y="departamento", data=DB_cases, palette="Set3",order=order_percent.index.tolist())
Out[9]:
Is there any tendency between age and the state?¶
- Yes, it seems that people who belong to Hospital, Hospital UCI and Fallecido category tend to have a higher median of age compare to people who belong to recuperado or casa category. In [10]:
plt.figure(figsize=(15,8))
ax = sns.boxplot(x="state", y="age", data=DB_cases,palette="Set3")
Is there any tendency between age and type of the register (Importado, Relacionado or en estudio)?¶
- No, it seems that there is not a tendency between age and type of register. In [11]:
plt.figure(figsize=(15,8))
ax = sns.boxplot(x="type", y="age", data=DB_cases,palette="Set3")
Is there any tendency between state and sex?¶
- It means that the proportion between F and M for 'Recuperado' and 'Casa'category is the same. A bit more for F than M. On the other hand, for 'Hospital', 'Hosputal UCI' and ' Fallecido' category the proportiion of F and M is different. There are more M then F. In [12]:
plt.figure(figsize=(15,8))
ax = sns.countplot(x="state", hue="sex", data=DB_cases,palette="Set3")
Is there any tendency between state, age and sex?¶
- For 'Recuperado', 'Casa', 'Hospital UCI' and 'Fallecido' there is not a clear tendency between age and sex. However for 'Hospital', it seems that M tend to have a in a lower age that F in 'Hospital' category.. In [13]:
plt.figure(figsize=(15,8))
ax = sns.boxplot(x="state", y="age", hue="sex",data=DB_cases, palette="Set3")
How each state bahave over the time?¶
- it's clear that people who are at home is increasing over the month, besides of people at the hospital who seems to decrease at the end of the month. In [14]:
#se determina el tamaño de la ilustración
fig, ax = plt.subplots(figsize=(15,7))
#Finalmente se genera el conteo por año de cada estado y se grafica
DB_cases.groupby([pd.DatetimeIndex(DB_cases.date).to_period("D"),'state']).count()['ID'].unstack().plot(ax=ax)
Out[14]:
What is the accomulative behavior of people at home?¶
- Seems that during march the tendency was exponential. In [15]:
df = DB_cases.copy()
df=df.assign(ones=1)
df=df.sort_values(['date']).reset_index(drop=True)
df["cum_ones"]=df.groupby(['state'])['ones'].cumsum(axis=0)
In [16]:
depar = ['Casa']
df_casa = df[ df['state'].isin(depar) ]
fig, ax = plt.subplots(figsize=(15,7))
df_casa.groupby([pd.DatetimeIndex(df_casa.date).to_period("D"),'state']).max()['cum_ones'].unstack().plot(ax=ax)
Out[16]:
What is the accomulative behavior for the other categories?¶
- Seems that people at the hospital is increasing exponencially. Likewise, people 'Hospital UCI' started exponencially but at the end of the month decrease a bit. Moreover, the increase of ´Fallecido' categories seems to be constant.
In [17]:
depar = ['Casa']
df_casa = df[~df['state'].isin(depar) ]
fig, ax = plt.subplots(figsize=(15,7))
#Finalmente se genera el conteo por año de cada estado y se grafica
df_casa.groupby([pd.DatetimeIndex(df_casa.date).to_period("D"),'state']).max()['cum_ones'].unstack().plot(ax=ax)
Out[17]:
What is the distribution of the type of registers?¶
- the imported cases have the highest number of cases, then 'Relacionado' has the second highest number and finally 'En estudio' with around 260 cases.
In [18]:
DB_cases.columns
Out[18]:
In [19]:
df_proce = DB_cases.copy()
order_percent = df_proce.type.value_counts(normalize = True)
print(order_percent)
plt.figure(figsize=(15,8))
sns.countplot(y="type", data=df_proce, palette="Set3",order=order_percent.index.tolist())
Out[19]:
What is the distribution of the type of registers and sex?¶
- 'Importando' category tend to have more M than F. On the contrary, relacionado category has more F tha M and the last one 'En estudio' is almost the same number for both.
In [20]:
plt.figure(figsize=(15,8))
ax = sns.countplot(x="type", hue="sex", data=DB_cases,palette="Set3")
What is the distribution of the type of register and State?¶
- Seems that 'En estudio' state there is not any person that has been recovered. For the others seem to have the same distribution by state.
In [21]:
plt.figure(figsize=(15,8))
ax = sns.countplot(x="type", hue="state", data=DB_cases,palette="Set3")
What is the distribution of imported people and procedence country?¶
- Spain, Unitedd States, Ecuador, mexico, Turquia and panama convers around 74% of all imported cases
In [22]:
depar = ['Importado']
df_proce_importado = df_proce[ df_proce['type'].isin(depar) ]
order_percent = df_proce_importado.procedence.value_counts(normalize = True)
print(order_percent)
plt.figure(figsize=(20,20))
sns.countplot(y="procedence", data=df_proce_importado, palette="Set3",order=order_percent.index.tolist())
Out[22]:
What is the distribution of 'En estudio' and 'Departamento'?¶
- Bogota, Valle del cauca, Antioquia Santa marta, cundinamarca and huila convers around 80% of all imported cases
In [23]:
depar = ['En estudio']
df_proce_importado = df_proce[ df_proce['type'].isin(depar) ]
order_percent = df_proce_importado.departamento.value_counts(normalize = True)
print(order_percent)
plt.figure(figsize=(20,20))
sns.countplot(y="departamento", data=df_proce_importado, palette="Set3",order=order_percent.index.tolist())
Out[23]:
What is the distribution of 'Relacionado' and 'Departamento'?¶
- Bogota, Valle del cauca, Antioquia, Cartagena, huila and risaralda convers around 70% of all imported cases
In [24]:
depar = ['Relacionado']
df_proce_importado = df_proce[ df_proce['type'].isin(depar) ]
order_percent = df_proce_importado.departamento.value_counts(normalize = True)
print(order_percent)
plt.figure(figsize=(20,20))
sns.countplot(y="departamento", data=df_proce_importado, palette="Set3",order=order_percent.index.tolist())
Out[24]:
What is the distribution of cities the top three of 'Departamentos'?¶
- Bogota, cali, medellin, palmera , envigado and rionegro convers around 82% of all imported cases
In [25]:
DB_cases_copy = DB_cases.copy()
depar = ['Bogota D.C.','Valle del Cauca','Antioquia']
DB_cases_copy = DB_cases_copy[ DB_cases_copy['departamento'].isin(depar) ]
order_percent_city = DB_cases_copy.city.value_counts(normalize = True)
print(order_percent_city)
plt.figure(figsize=(15,8))
sns.countplot(y="city", data=DB_cases_copy, palette="Set3", order = order_percent_city.index.tolist())
Out[25]:
What is the distribution of 'Departamento', 'state' and 'age'?¶
- According to the following chart, as higher the age is, worst will be the state either ' Hospital', 'Hospital UCI' or 'Fallecido'
In [26]:
plt.figure(figsize=(15,8))
ax = sns.boxplot(x="departamento", y="age", hue="state",data=DB_cases_copy, palette="Set3")
Segmentation model¶
- This is an Unsupervised model that allow you to identify groups of registers with similar features. After identifing these groups, several strategies could be created and adjusted subject the group selected. The most common method is K-means. This one works really well for numerical values but not good for mixing data. that is why I used a data transformation method in order to take into account numerical and categoriacal variables. This method is named as Factor Analysis of mixed data (FAMD). In [27]:
DB_cases.columns
Out[27]:
- Data_k is created in order to save the data that will be used to run the model.
In [28]:
Data_k = DB_cases[['city','departamento','state','age','sex','type','procedence']]
FAMD method¶
- As there are numerical variables such as 'Age' and categorical variables such as 'state', the best method to manage this mix is named: Factor analysis of mixed data that basically has as an output, numerical values by mixing numerical variables with the effect of the categorical variables.
In this case I created two different components and plot it
In [29]:
import prince
famd = prince.FAMD(
n_components=2,
n_iter=3,
copy=True,
check_input=True,
engine='auto',
random_state=42
)
famd = famd.fit(Data_k)
l = famd.row_coordinates(Data_k)
print(l[0])
sns.relplot(x=l[0], y=l[1])
Out[29]:
- FAMD Components converted into Data frame
In [30]:
principalDf = pd.DataFrame(data = l
, columns = ['FAMD_component_1','FAMD_component_2'])
principalDf.shape
principalDf['FAMD_component_1'] = l[0]
principalDf['FAMD_component_2'] = l[1]
print(principalDf)
Implementation of method to determine the optimum number of clusters. Elbow method and silhouette method¶
- According to elbow method, seems that the optimal number of cluster is 4 or 5
In [31]:
kmeans_kwargs = {
"init": "random",
"n_init": 10,
"max_iter": 300,
"random_state": 42,
}
sse = {}
for k in tqdm(range(2, 30)):
kmeans = KMeans(n_clusters=k, **kmeans_kwargs).fit(principalDf)
sse[k] = kmeans.inertia_ # Inertia: Sum of distances of samples to their closest cluster center
fig = go.Figure(data=go.Scatter(x=list(sse.keys()), y=list(sse.values())))
fig.show()
- According to silhouette method, seems that the optimal number of cluster is 4
In [32]:
from sklearn.metrics import silhouette_score
# A list holds the silhouette coefficients for each k
silhouette_coefficients = []
# Notice you start at 2 clusters for silhouette coefficient
for k in range(2, 30):
kmeans = KMeans(n_clusters=k, **kmeans_kwargs)
kmeans.fit(principalDf)
score = silhouette_score(principalDf, kmeans.labels_)
silhouette_coefficients.append(score)
plt.style.use("fivethirtyeight")
plt.plot(range(2, 30), silhouette_coefficients)
plt.xticks(range(2, 30))
plt.xlabel("Number of Clusters")
plt.ylabel("Silhouette Coefficient")
plt.show()
- Runing k-means using the two variables that I got using FAMD method and including, as a parameter, 4 clusters.
In [18]:
data = principalDf
for c in data.columns:
pt = PowerTransformer()
data.loc[:, c] = pt.fit_transform(np.array(data[c]).reshape(-1, 1))
kmeans = KMeans(n_clusters=4,max_iter= 1000000).fit(data)
kmeans_labels = kmeans.labels_
pd.Series(kmeans_labels).value_counts()
Out[18]:
- I assigned the result of the clustering to the current dataset with the components and I plotted in order to know if there is a clear separation among the clusters.
In [19]:
lgbm_data = principalDf.copy()
lgbm_data['Clusters_k_means'] = kmeans_labels
print(lgbm_data)
print(lgbm_data.columns)
sns.relplot(x='FAMD_component_1', y='FAMD_component_2', hue='Clusters_k_means', style='Clusters_k_means',data=lgbm_data, palette="Paired")
Out[19]:
- Finally, the clusters are assigned to the original data in order to know the effect of each variable.
In [22]:
fin_lgbm_data = DB_cases.copy()
fin_lgbm_data['Clusters_k_means'] = kmeans_labels
In [23]:
fin_lgbm_data.head()
Out[23]:
Effect of numerical variables¶
- Clearly all clusters could be identified by the range of age. Cluster 1 and 3 has higher range of age than 0 and 2 cluster. In [24]:
numerical_sig = ['age']
for i in numerical_sig:
plt.figure(figsize=(15,8))
ax = sns.boxplot(x="Clusters_k_means", y=i, data=fin_lgbm_data)
ax2 = sns.catplot(x="Clusters_k_means", y=i, data=fin_lgbm_data)
Effect of categorical variables¶
- As cluster 1 and 3 are really similar regarding the range of age, those could be idenfied because cluster 3 considers just 'importado' people but cluster 1 considers 'Relacionado' and 'En estudio'
- As cluster 0 and 2 are really similar regarding the range of age, Those could be identified because cluster 2 considers 'Relacionado' or ' En estudio'and cluster 0 consider monstly 'Importado'. In addition, the distribution of F and M change between cluster 0 and 2. In [27]:
categorical_sig = ['state','sex','type']
for i in categorical_sig:
plt.figure(figsize=(10,4)) #this creates a new figure on which your plot will appear
sns.countplot(y="Clusters_k_means", hue=i, data=fin_lgbm_data)
